package com.example.smartf.service.impl;

import com.example.smartf.model.PyEntity;
import com.example.smartf.model.mysql.Formulainfo;
import com.example.smartf.model.mysql.Traindata;
import com.example.smartf.repository.mysql.FormulainfoRepository;
import com.example.smartf.repository.mysql.TraindataRepository;
import com.example.smartf.service.TraindataService;
import com.example.smartf.tool.PythonServer;
import com.example.smartf.tool.PythonUtils;
import com.example.smartf.tool.RedisUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.util.*;

@Service
public class TraindataServiceImpl implements TraindataService {

    @Autowired
    private TraindataRepository traindataRepository;
    @Autowired
    private PythonUtils pythonUtils;
    @Autowired
    private RedisUtils redisUtils;
    @Autowired
    private PythonServer pythonServer;
    @Autowired
    private FormulainfoRepository formulainfoRepository;

    //日志记录器
    private Logger logger = LoggerFactory.getLogger(getClass());

    private final String excelpath = "/root/wordspace/Exceltemp/";
    //private final String excelpath = "D:\\";

    //查询一个设备的机器学习 训练数据
    @Override
    public List<Traindata> showdeviceTraindata(String deviceID){
        return traindataRepository.findAllByTraindataKeyDeviceIDOrderByTraindataKey_Days(deviceID);
    }

    //更新一条设备训练数据 的Cgr 标注结果
    @Override
    public String updatedeviceCgr(String deviceID, Integer day, Double cgr){
        if( traindataRepository.updateCgr(deviceID,day,cgr) == 1){
            return "success";
        }else {
            return "failed";
        }
    }

    @Override
    public void startTrain(String username,String deviceID, List<Integer> days){
        //使用exec终端执行
//        String[] daysArray = new String[days.size()];
//        for (int i = 0 ;i < days.size(); i++){
//            daysArray[i] = days.get(i).toString();
//        }
//        //调用python 程序执行方法
//        pythonUtils.pythonExec(username,deviceID,daysArray);

        //使用python服务器执行 Flask
        Integer[] days_array = (Integer[]) days.toArray();
        PyEntity pyEntity = new PyEntity(deviceID,days_array);
        pythonServer.trainmodel(pyEntity);
    }

    @Override
    public String upLoadExcel(Workbook workbook, String filename) throws Exception{
        Sheet sheet = null;
        //取出第一个sheet
        sheet = workbook.getSheetAt(0);

        //检查所有行 列数相同
        for(int i = 0 ; i < sheet.getLastRowNum()-1; i++){
            if( sheet.getRow(i).getLastCellNum() != sheet.getRow(i+1).getLastCellNum()){
                return "wrong format";
            }
        }
        for( int i = 0 ; i < sheet.getRow(0).getLastCellNum(); i++){
            if( isNumber(readExcelCell(sheet,0,i)) == true ){
                return "wrong format";
            }
        }
        //检查 第二行到最后一行 是否都为数值 且 不能为空
        for( int i = 1; i < sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);
            for (int j = 0 ; j < row.getLastCellNum(); j++){
                String temp = readExcelCell(sheet,i,j);
                if ( temp!= null && temp.length() > 0 && isNumber(temp) == true ){

                } else {
                    return "wrong format";
                }
            }
        }


        //准备路径和 输出流对象
        File outfile = new File(excelpath);
        OutputStream stream = null;

        //写入文件进入磁盘
        try {
            stream = new FileOutputStream(new File(outfile,filename));
            workbook.write(stream);
        }catch (FileNotFoundException e){
            e.printStackTrace();
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            //关闭流对象
            if(stream != null){
                try {
                    stream.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }
        }

        logger.info("Excel文件上传成功, 文件名："+ filename );
        //检查结束 开始调用python去解析excel文件
        String ret = new String();
        try {
            ret = pythonServer.dealExcel(excelpath+filename);
        }catch (Exception e){
            e.printStackTrace();
            workbook.close();
            //删除文件
            if( outfile.exists() == true) {
                outfile.delete();
            }
            return "time out";
        }

        workbook.close();
        if( ret == null ){
            //删除文件
            if( outfile.exists() == true) {
                outfile.delete();
            }
            return "time out";
        }else {
            logger.info("Excel文件 生成模型成功");
            //删除文件
            if( outfile.exists() == true) {
                outfile.delete();
            }
            return ret;
        }

    }

    @Override
    public List<Formulainfo> showFormulas(String type) {
        return formulainfoRepository.findByType(type);
    }

    @Override
    public void applyFormula(String type, String formula) {
        Formulainfo formulainfo = new Formulainfo(type,formula);
        formulainfoRepository.save(formulainfo);

    }

    @Override
    public String delFormula(Integer id) {
        int ret = formulainfoRepository.deleteById(id);
        if( ret == 1){
            return "success";
        }else {
            return "failed";
        }
    }

    //读取一个单元格 转化成 String 字符串
    private String readExcelCell(Sheet sheet,int row,int col){
        String result=null;
        try {
            Cell cell = sheet.getRow(row).getCell(col);
            result = cell.toString();
        } catch (Exception e) {
            e.printStackTrace();
            result = null;
        }
        return result;
    }

    //判断一个String是否为数字
    private Boolean isNumber(String str){
        try{
            Double.parseDouble(str);
            return true;
        }catch (NumberFormatException e){
            return false;
        }
    }
}
